USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[KonseiKobetu2] スクリプト日付: 2014/05/16 11:21:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KonseiKobetu]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KonseiKobetu]
GO

CREATE PROCEDURE [dbo].[KonseiKobetu]
	@TAIKAINO int,
	@KYOGISYANO int,
	@KYOGINO int

AS
	SELECT 
		KOBE.KYOGISYANO,
		dbo.Kaze(SYUMO.SYUMOKUKUBUN,8,dbo.Kiroku(KOBE.KIROKU,SYUMO.SYUMOKUKUBUN,SYUMO.KIROKUKUBUN,KOBE.KIROKUFLG),KOBE.KAZE )as KIROKUALL,
		KOBE.KIROKU,
		KOBE.TOKUTEN,
		KOBE.SYUKKETU,
		KOBE.TOKUTENFLG,
		SYUMO.SYUMOKUNO
		
	FROM
		TBL_KONSEI_KOBETU AS KOBE
		,TBL_SYUMOKU_MASTER SYUMO
		,TBL_KONSEI_MASTER AS BB 
		,TBL_KYOGI AS KYO

	WHERE
		KOBE.KYOGISYANO = @KYOGISYANO
		AND SYUMO.SYUMOKUNO = KOBE.SYUMOKUNO
		AND BB.SYUMOKUNO = KYO.SYUMOKUNO 
		AND BB.KONSEINO = SYUMO.SYUMOKUNO
		AND KYO.TAIKAINO = @TAIKAINO
		AND KYO.KYOGINO = @KYOGINO
		AND KOBE.TAIKAINO = KYO.TAIKAINO 
		AND KOBE.KYOGINO = KYO.KYOGINO
	ORDER BY BB.ID